selecting records by timestamp (date)

--0-483084940-1141150586=:38148
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

I have the following table running on postgres 8.04:

CREATE TABLE ValueLog=20
(=20
idValueLog int4 NOT NULL DEFAULT nextval ('public.idValueLog_seq'::=
text),=20
PPx integer,=20
iValue integer DEFAULT -1,=20
fValue real DEFAULT -1,=20
t_tag timestamp without time zone,=20
t_arr timestamp without time zone,=20
cot integer,=20
ack boolean,=20
blk boolean,=20
qd_iv boolean,=20
qd_nt boolean,=20
qd_sb boolean,=20
qd_bl boolean,=20
qd_ov boolean,=20
CONSTRAINT idValueLog PRIMARY KEY (idValueLog)=20
)=20
WITHOUT OIDS;=20
ALTER TABLE ValueLog OWNER TO $admin_user;"=20

I've created the following SQL statement to select the records newer then=
a given date:

select almvalue.almname , ppoint.ppdimstr, valuelog.ack, valuelog.blk, to=
_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from ppoint, valuelog, almv=
alue where ppoint.ppx =3D valuelog.ppx and almvalue.almvalue =3D valuelog=
..ivalue and valuelog.ppx =3D $db_ppx and to_char(valuelog.t_arr,'YYYYMMD=
D') >=3D '$ts

$ts is a string, the date value input by the user and formated YYYYMMDD

I've developed the web site on my Ubuntu box, but when I uploaded the sit=
e on the server Fedora Core 3, I had problems with the date filtering. Ru=
nning psql on the server I've noticed that there were more data in the se=
lect * from value log results, field t_arr something like MM-DD-YYYY-dsf=
sdfd (something else which I don't know what it is)
I think that the local time settings on the server makes me the troubles.

What's the solution?

I've even checked the result of:
select to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from valuelog;

but instead of data I got empty data set. Why?

What's the best way to do filtering (selecting) data by time?

TIA, Jovan
=09
---------------------------------
Yahoo! Mail
Bring photos to life! New PhotoMail makes sharing a breeze.=20
--0-483084940-1141150586=:38148
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

 Hi,

I have the following table running on postgres 8.04:
=

   CREATE TABLE ValueLog
    (
 =
;     idValueLog int4 NOT NULL DEFAULT nextval ('publ=
ic.idValueLog_seq'::text),
      PPx integer=
,
      iValue integer DEFAULT -1,
 =
;     fValue real DEFAULT -1,
   &=
nbsp;  t_tag timestamp without time zone,
   &nbs=
p;  t_arr timestamp without time zone,
    &=
nbsp; cot integer,
      ack boolean,
&n=
bsp;     blk boolean,
    &nb=
sp; qd_iv boolean,
      qd_nt boolean,
=
      qd_sb boolean,
    =
;  qd_bl boolean,
      qd_ov boolean, =

      CONSTR
AINT=20
idValueLog PRIMARY KEY (idValueLog)
    )
 &=
nbsp;      WITHOUT OIDS;
     =
;   ALTER TABLE ValueLog OWNER TO $admin_user;"

I've cr=
eated the following SQL statement to select the records newer then a give=
n date:

select almvalue.almname , ppoint.ppdimstr, valuelog.ack, v=
aluelog.blk, to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from ppoint,=
valuelog, almvalue where ppoint.ppx =3D valuelog.ppx and almvalue.almval=
ue =3D valuelog.ivalue and valuelog.ppx =3D $db_ppx and  to_char(val=
uelog.t_arr,'YYYYMMDD') >=3D '$ts

$ts is a string, the date val=
ue input by the user and formated YYYYMMDD

I've developed the web =
site on my Ubuntu box, but when I uploaded the site on the server Fedora =
Core 3, I had problems with the date filtering. Running psql on the serve=
r I've noticed that there were more data in the select * from  value=
log results, field t_arr something like
MM-DD-YYYY-dsfsdfd (something else which I don't know what it is)
I t=
hink that the local time settings on the server makes me the troubles. >
What's the solution?

I've even checked the result  of: r>select to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from  value=
log;

but instead of data I got empty data set. Why?

What's =
the best way to do filtering (selecting) data by time?

TIA, Jovan<=
p>

Re: selecting records by timestamp (date)

------=_Part_15266_33434020.1141156261685
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

maybe the postgres version in the server is different

On 2/28/06, Jovan Kostovski wrote:
>
> Hi,
>
> I have the following table running on postgres 8.04:
>
> CREATE TABLE ValueLog
> (
> idValueLog int4 NOT NULL DEFAULT nextval ('
> public.idValueLog_seq'::text),
> PPx integer,
> iValue integer DEFAULT -1,
> fValue real DEFAULT -1,
> t_tag timestamp without time zone,
> t_arr timestamp without time zone,
> cot integer,
> ack boolean,
> blk boolean,
> qd_iv boolean,
> qd_nt boolean,
> qd_sb boolean,
> qd_bl boolean,
> qd_ov boolean,
> CONSTR AINT idValueLog PRIMARY KEY (idValueLog)
> )
> WITHOUT OIDS;
> ALTER TABLE ValueLog OWNER TO $admin_user;"
>
> I've created the following SQL statement to select the records newer then
> a given date:
>
> select almvalue.almname , ppoint.ppdimstr, valuelog.ack, valuelog.blk,
> to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from ppoint, valuelog,
> almvalue where ppoint.ppx =3D valuelog.ppx and almvalue.almvalue =3D
> valuelog.ivalue and valuelog.ppx =3D $db_ppx and to_char(valuelog.t_arr,=
'YYYYMMDD')
> >=3D '$ts
>
> $ts is a string, the date value input by the user and formated YYYYMMDD
>
> I've developed the web site on my Ubuntu box, but when I uploaded the sit=
e
> on the server Fedora Core 3, I had problems with the date filtering. Runn=
ing
> psql on the server I've noticed that there were more data in the select *
> from value log results, field t_arr something like MM-DD-YYYY-dsfsdfd
> (something else which I don't know what it is)
> I think that the local time settings on the server makes me the troubles.
>
> What's the solution?
>
> I've even checked the result of:
> select to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from valuelog;
>
> but instead of data I got empty data set. Why?
>
> What's the best way to do filtering (selecting) data by time?
>
> TIA, Jovan
>
> ------------------------------
> Yahoo! Mail
> Bring photos to life! New PhotoMail
> l.mail.yahoo.com>makes sharing a breeze.
>



--
Atte. Rodrigo A. Llanos N.

Saludos.

------=_Part_15266_33434020.1141156261685
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

maybe the postgres version in the server is different

Re: selecting records by timestamp (date)

This is a multi-part message in MIME format.

------_=_NextPart_001_01C63CA6.0B1D8DF6
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Would probably be best to change the input data the user is entering
(seems to be a webpage yes?) to the format YYYY-MM-DD, and don't cast
the timestamp value in the where clause. Also, note that your create
table statement lists your table name as "ValueLog" - try and make sure
that you aren't having case problems. You may need to enclose the table
name in double quotes to keep the case on the query. Also, with your
test query where you are selecing just that field, take the table name
off of the field, and also try and run a "SELECT COUNT(*) FROM ValueLog"
to make sure that you have records to return.
=20
Beware also that you aren't connecting to a separate server that has the
schema but no data on your connection where you are issuing the query.
=20

Jason Minion=20
=20

________________________________

From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of Rodrigo Llanos
Sent: Tuesday, February 28, 2006 1:51 PM
To: pgsql-php@postgresql.org
Subject: Re: [PHP] selecting records by timestamp (date)



maybe the postgres version in the server is different


On 2/28/06, Jovan Kostovski wrote:=20

Hi,
=09
I have the following table running on postgres 8.04:
=09
CREATE TABLE ValueLog=20
(=20
idValueLog int4 NOT NULL DEFAULT nextval
('public.idValueLog_seq'::text),=20
PPx integer,=20
iValue integer DEFAULT -1,=20
fValue real DEFAULT -1,=20
t_tag timestamp without time zone,=20
t_arr timestamp without time zone,=20
cot integer,=20
ack boolean,=20
blk boolean,=20
qd_iv boolean,=20
qd_nt boolean,=20
qd_sb boolean,=20
qd_bl boolean,=20
qd_ov boolean,=20
CONSTR AINT idValueLog PRIMARY KEY (idValueLog)=20
)=20
WITHOUT OIDS;=20
ALTER TABLE ValueLog OWNER TO $admin_user;"=20
=09
I've created the following SQL statement to select the records
newer then a given date:=20
=09
select almvalue.almname , ppoint.ppdimstr, valuelog.ack,
valuelog.blk, to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from
ppoint, valuelog, almvalue where ppoint.ppx =3D valuelog.ppx and
almvalue.almvalue =3D valuelog.ivalue and valuelog.ppx =3D $db_ppx and
to_char(valuelog.t_arr,'YYYYMMDD') >=3D '$ts
=09
$ts is a string, the date value input by the user and formated
YYYYMMDD
=09
I've developed the web site on my Ubuntu box, but when I
uploaded the site on the server Fedora Core 3, I had problems with the
date filtering. Running psql on the server I've noticed that there were
more data in the select * from value log results, field t_arr something
like MM-DD-YYYY-dsfsdfd (something else which I don't know what it is)
I think that the local time settings on the server makes me the
troubles.
=09
What's the solution?
=09
I've even checked the result of:
select to_char( valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from
valuelog;
=09
but instead of data I got empty data set. Why?
=09
What's the best way to do filtering (selecting) data by time?
=09
TIA, Jovan=20

=09
________________________________

Yahoo! Mail
Bring photos to life! New PhotoMail
l
..mail.yahoo.com> makes sharing a breeze.=20

=09




--=20
Atte. Rodrigo A. Llanos N.

Saludos.=20

------_=_NextPart_001_01C63CA6.0B1D8DF6
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



charset=3Dus-ascii">

Jason Minion 
class=3D588442820-28022006> size=3D2>  class=3D588442820-28022006> 

From: =
pgsql-php-owner@postgresql.org=20
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of Rodrigo=20
Llanos
Sent: Tuesday, February 28, 2006 1:51 PM
To:=20
pgsql-php@postgresql.org
Subject: Re: [PHP] selecting records =
by=20
timestamp (date)